package net.esj.basic.dao.jdbc;

import java.util.Date;

import net.esj.basic.utils.DateFormat;

public class JdbcSqlUtils {
	
	public static String generateSqlNoOrder(String sql){
		if(sql.toUpperCase().contains("ORDER BY")){
			int i = sql.toUpperCase().lastIndexOf("ORDER BY");
			int j = sql.toUpperCase().lastIndexOf("ASC");
			int k = sql.toUpperCase().lastIndexOf("DESC");
			if(j>i){
				sql = sql.substring(0, i)+sql.substring(j+3, sql.length());
			}else if(k>i){
				sql = sql.substring(0, i)+sql.substring(k+4, sql.length());
			}
		}
		return sql;
	}
	
	public static String generateSelectCountSql(String sql){
		StringBuilder sb = new StringBuilder();
		sql = generateSqlNoOrder(sql);
		sb.append("SELECT COUNT(*) AS COUNT FROM (").append(sql).append(") AS count_result ");
		return sb.toString();
	}
	
	/**
	 * 用于MYSQL分页查询
	 * @param sql
	 * @param start
	 * @param end
	 * @return
	 */
	public static String generatePageSql(String sql,int start,int end){
		StringBuilder sb = new StringBuilder(sql);
		sb.append(" limit ").append(start).append(" , ").append(end);
		return sb.toString();
	}

	/**
	 * 用于SQLServer分页查询
	 * @param sql
	 * @param start
	 * @param end
	 * @param orderStr 与sql的order相同
	 * @return
	 */
	public static String generatePageSql(String sql,int start,int end, String orderStr){
		if(sql.toUpperCase().contains("ORDER BY")){
			int i = sql.toUpperCase().lastIndexOf("ORDER BY");
			int j = sql.toUpperCase().lastIndexOf("ASC");
			int k = sql.toUpperCase().lastIndexOf("DESC");
			if(j>i){
				sql = sql.substring(0, i)+sql.substring(j+3, sql.length());
			}else if(k>i){
				sql = sql.substring(0, i)+sql.substring(k+4, sql.length());
			}
		}
		StringBuilder sb = new StringBuilder(sql);
		String ret = "select * from (" +
				" select row_number() over("+orderStr+") as row_number, * from ("+sb.toString()+") b ) a" +
						" where row_number between "+start+" and "+end;
//		sb.append(" limit ").append(start).append(" , ").append(end);
		return ret;
	}
	
	public static String resolveSqlCondition(Object cond){
		if(cond instanceof Number){
			return ((Number)cond).toString();
		}
		if(cond instanceof Date){
			String dateStr  =DateFormat.toString((Date)cond, DateFormat.DEFAULT_DATETIME_FORMAT_SEC);
			return "'"+dateStr+"'";
		}
		if(cond instanceof Boolean){
			if((Boolean)cond){
				return "1";
			}else{
				return "0";
			}
		}
		String str = cond.toString();
		return "'"+str+"'";
	}
}
